package com.sqlcheck.service.impl;

import com.sqlcheck.common.enums.DatabaseType;
import com.sqlcheck.dto.request.UnifiedAnalysisRequest;
import com.sqlcheck.dto.response.*;
import com.sqlcheck.entity.ScanResult;
import com.sqlcheck.model.*;
import com.sqlcheck.service.*;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
@RequiredArgsConstructor
public class UnifiedAnalysisServiceImpl implements UnifiedAnalysisService {

    private final SqlParsingService sqlParsingService;
    private final CompatibilityRuleEngine ruleEngine;

    @Override
    public UnifiedAnalysisResponse performLayeredAnalysis(UnifiedAnalysisRequest request) {
        long startTime = System.currentTimeMillis();

        try {
            log.info("开始执行统一分析 - 用户ID: {}, AI增强: {}",
                    request.getUserId(), request.isAiEnhancementEnabled());

            // 检查是否为相同数据库类型
            if (request.getSourceDbType().equals(request.getTargetDbType())) {
                return createSameDatabaseResponse(request, startTime);
            }

            // 1. 解析SQL语句
            List<SqlStatement> sqlStatements = sqlParsingService.parseUserSql(request.getSqlContent());
            if (sqlStatements.isEmpty()) {
                throw new RuntimeException("未能解析出有效的SQL语句");
            }

            // 2. 执行规则分析
            List<ScanResult> allIssues = new ArrayList<>();
            for (SqlStatement sqlStatement : sqlStatements) {
                try {
                    List<ScanResult> scanResults = ruleEngine.checkCompatibility(
                            sqlStatement, request.getSourceDbType(), request.getTargetDbType(), null);

                    allIssues.addAll(scanResults);
                } catch (Exception e) {
                    log.error("规则分析失败: content={}", sqlStatement.getContent(), e);
                }
            }

            // 3. 构建响应
            return UnifiedAnalysisResponse.builder()
                    .success(true)
                    .sqlStatements(sqlStatements)
                    .issues(allIssues)
                    .statistics(buildSimpleStatisticsFromScanResults(allIssues, new ArrayList<>()))
                    .analysisTime(LocalDateTime.now())
                    .duration(System.currentTimeMillis() - startTime)
                    .build();

        } catch (Exception e) {
            log.error("统一分析失败", e);
            return UnifiedAnalysisResponse.builder()
                    .success(false)
                    .errorMessage("统一分析失败: " + e.getMessage())
                    .analysisTime(LocalDateTime.now())
                    .duration(System.currentTimeMillis() - startTime)
                    .build();
        }
    }

    private UnifiedAnalysisResponse createSameDatabaseResponse(UnifiedAnalysisRequest request, long startTime) {
        try {
            // 解析SQL语句
            List<SqlStatement> sqlStatements = sqlParsingService.parseUserSql(request.getSqlContent());

            // 创建提示信息（使用ScanResult）
            ScanResult infoResult = new ScanResult();
            infoResult.setIssueType(ScanResult.IssueType.COMPATIBILITY_WARNING);
            infoResult.setSeverity(ScanResult.Severity.INFO);
            infoResult.setIssueDescription("源数据库和目标数据库类型相同，无需兼容性分析");
            infoResult.setSuggestion("当前SQL语句可以直接在目标数据库中使用");
            infoResult.setSqlContent(request.getSqlContent());
            infoResult.setLineNumber(1);
            infoResult.setColumnNumber(1);
            infoResult.setAnalysisEngine("RULE");

            List<ScanResult> issues = Arrays.asList(infoResult);
            List<OptimizationSuggestion> optimizations = new ArrayList<>();

            // 构建统计信息
            UnifiedAnalysisResponse.AnalysisStatistics statistics = buildSimpleStatisticsFromScanResults(issues,
                    optimizations);

            return UnifiedAnalysisResponse.builder()
                    .success(true)
                    .sqlStatements(sqlStatements)
                    .issues(issues)
                    .optimizations(optimizations)
                    .statistics(statistics)
                    .analysisTime(LocalDateTime.now())
                    .duration(System.currentTimeMillis() - startTime)
                    .build();

        } catch (Exception e) {
            log.error("处理相同数据库类型分析失败", e);
            return UnifiedAnalysisResponse.builder()
                    .success(false)
                    .errorMessage("分析过程中发生错误: " + e.getMessage())
                    .analysisTime(LocalDateTime.now())
                    .duration(System.currentTimeMillis() - startTime)
                    .build();
        }
    }

    @Override
    public List<DatabaseTypeResponse> getSupportedDatabases() {
        return Arrays.stream(DatabaseType.values())
                .map(dbType -> DatabaseTypeResponse.builder()
                        .value(dbType.name())
                        .label(getDatabaseLabel(dbType))
                        .icon("database")
                        .supportAsSource(true)
                        .supportAsTarget(true)
                        .build())
                .collect(Collectors.toList());
    }

    @Override
    public List<SqlExampleResponse> getSqlExamples(String category, String sourceDbType) {
        // 这里返回硬编码的示例，实际项目中可以从数据库获取
        List<SqlExampleResponse> examples = new ArrayList<>();

        examples.add(SqlExampleResponse.builder()
                .id("oracle-basic")
                .name("Oracle基础查询")
                .description("包含Oracle特有函数的基础查询")
                .category("BASIC")
                .difficulty("BASIC")
                .sourceDbType("ORACLE")
                .content("-- Oracle示例SQL\n" +
                        "SELECT\n" +
                        "    u.user_id,\n" +
                        "    u.username,\n" +
                        "    NVL2(u.email, u.email, 'N/A') as email_display,\n" +
                        "    ROWNUM as row_num\n" +
                        "FROM users u\n" +
                        "WHERE ROWNUM <= 10\n" +
                        "ORDER BY u.created_date NULLS LAST;")
                .expectedIssues(3)
                .createdTime(LocalDateTime.now())
                .build());

        examples.add(SqlExampleResponse.builder()
                .id("mysql-basic")
                .name("MySQL基础查询")
                .description("包含MySQL特有函数的基础查询")
                .category("BASIC")
                .difficulty("BASIC")
                .sourceDbType("MYSQL")
                .content("-- MySQL示例SQL\n" +
                        "SELECT\n" +
                        "    u.user_id,\n" +
                        "    u.username,\n" +
                        "    JSON_EXTRACT(u.profile, '$.email') as email,\n" +
                        "    u.created_date\n" +
                        "FROM users u\n" +
                        "WHERE u.status = 'active'\n" +
                        "ORDER BY u.created_date DESC\n" +
                        "LIMIT 10;")
                .expectedIssues(2)
                .createdTime(LocalDateTime.now())
                .build());

        examples.add(SqlExampleResponse.builder()
                .id("goldendb-basic")
                .name("GoldenDB基础查询")
                .description("标准SQL查询示例")
                .category("BASIC")
                .difficulty("BASIC")
                .sourceDbType("GOLDENDB")
                .content("-- GoldenDB示例SQL\n" +
                        "SELECT\n" +
                        "    u.user_id,\n" +
                        "    u.username,\n" +
                        "    COALESCE(u.email, 'N/A') as email_display,\n" +
                        "    u.created_date\n" +
                        "FROM users u\n" +
                        "WHERE u.status = 1\n" +
                        "ORDER BY u.created_date DESC\n" +
                        "LIMIT 10;")
                .expectedIssues(0)
                .createdTime(LocalDateTime.now())
                .build());

        // 根据条件过滤
        return examples.stream()
                .filter(example -> category == null || category.equals(example.getCategory()))
                .filter(example -> sourceDbType == null || sourceDbType.equals(example.getSourceDbType()))
                .collect(Collectors.toList());
    }

    @Override
    public FormatSqlResponse formatSql(String sqlContent, String dbType) {
        long startTime = System.currentTimeMillis();

        try {
            // 简单的SQL格式化实现
            String formattedSql = formatSqlContent(sqlContent);

            return FormatSqlResponse.builder()
                    .success(true)
                    .formattedSql(formattedSql)
                    .duration(System.currentTimeMillis() - startTime)
                    .build();

        } catch (Exception e) {
            log.error("SQL格式化失败", e);
            return FormatSqlResponse.builder()
                    .success(false)
                    .errorMessage("格式化失败: " + e.getMessage())
                    .duration(System.currentTimeMillis() - startTime)
                    .build();
        }
    }

    @Override
    public boolean validateSqlSyntax(String sqlContent, String dbType) {
        try {
            List<SqlStatement> statements = sqlParsingService.parseUserSql(sqlContent);
            return !statements.isEmpty();
        } catch (Exception e) {
            log.debug("SQL语法验证失败: {}", e.getMessage());
            return false;
        }
    }

    // 辅助方法
    private String getDatabaseLabel(DatabaseType dbType) {
        switch (dbType) {
            case MYSQL:
                return "MySQL";
            case ORACLE:
                return "Oracle";
            case GOLDENDB:
                return "GoldenDB";
            default:
                return dbType.name();
        }
    }

    private String formatSqlContent(String sqlContent) {
        // 简单的SQL格式化逻辑
        return sqlContent
                .replaceAll("(?i)\\bSELECT\\b", "SELECT")
                .replaceAll("(?i)\\bFROM\\b", "FROM")
                .replaceAll("(?i)\\bWHERE\\b", "WHERE")
                .replaceAll("(?i)\\bORDER BY\\b", "ORDER BY")
                .replaceAll("(?i)\\bGROUP BY\\b", "GROUP BY")
                .replaceAll("(?i)\\bHAVING\\b", "HAVING")
                .replaceAll("(?i)\\bINSERT\\b", "INSERT")
                .replaceAll("(?i)\\bUPDATE\\b", "UPDATE")
                .replaceAll("(?i)\\bDELETE\\b", "DELETE")
                .replaceAll("\\s+", " ")
                .trim();
    }

    /**
     * 转换AI分析结果为优化建议格式
     */
    private List<OptimizationSuggestion> convertAIOptimizations(AIAnalysisResponse aiResponse) {
        List<OptimizationSuggestion> optimizations = new ArrayList<>();

        if (aiResponse.getStatementAnalysis() == null) {
            return optimizations;
        }

        for (AIStatementAnalysis statementAnalysis : aiResponse.getStatementAnalysis()) {
            if (statementAnalysis.getOptimizedStatement() != null &&
                    !statementAnalysis.getOptimizedStatement().equals(statementAnalysis.getOriginalStatement())) {

                OptimizationSuggestion optimization = OptimizationSuggestion.builder()
                        .id(UUID.randomUUID().toString())
                        .type(OptimizationSuggestion.OptimizationType.SYNTAX_ADJUST)
                        .description("AI建议的完整语句优化")
                        .originalCode(statementAnalysis.getOriginalStatement())
                        .optimizedCode(statementAnalysis.getOptimizedStatement())
                        .lineNumber(1)
                        .columnNumber(1)
                        .impact(OptimizationSuggestion.ImpactLevel.MEDIUM)
                        .confidence(0.85)
                        .explanation("基于AI分析的SQL语句优化建议")
                        .build();

                optimizations.add(optimization);
            }
        }

        return optimizations;
    }

    /**
     * 从ScanResult列表构建统计信息
     */
    private UnifiedAnalysisResponse.AnalysisStatistics buildSimpleStatisticsFromScanResults(
            List<ScanResult> issues,
            List<OptimizationSuggestion> optimizations) {

        int totalIssues = issues.size();
        int highSeverityCount = (int) issues.stream()
                .filter(i -> i.getSeverity() == ScanResult.Severity.HIGH).count();
        int mediumSeverityCount = (int) issues.stream()
                .filter(i -> i.getSeverity() == ScanResult.Severity.MEDIUM).count();
        int lowSeverityCount = (int) issues.stream()
                .filter(i -> i.getSeverity() == ScanResult.Severity.LOW).count();
        int infoSeverityCount = (int) issues.stream()
                .filter(i -> i.getSeverity() == ScanResult.Severity.INFO).count();

        int syntaxIssueCount = (int) issues.stream()
                .filter(i -> i.getIssueType() == ScanResult.IssueType.SYNTAX_ERROR).count();
        int compatibilityIssueCount = (int) issues.stream()
                .filter(i -> i.getIssueType() == ScanResult.IssueType.COMPATIBILITY_WARNING).count();
        int performanceIssueCount = (int) issues.stream()
                .filter(i -> i.getIssueType() == ScanResult.IssueType.PERFORMANCE_ISSUE).count();
        int securityIssueCount = (int) issues.stream()
                .filter(i -> i.getIssueType() == ScanResult.IssueType.SECURITY_RISK).count();

        return UnifiedAnalysisResponse.AnalysisStatistics.builder()
                .totalIssues(totalIssues)
                .highSeverityCount(highSeverityCount)
                .mediumSeverityCount(mediumSeverityCount)
                .lowSeverityCount(lowSeverityCount)
                .infoSeverityCount(infoSeverityCount)
                .optimizationCount(optimizations.size())
                .syntaxIssueCount(syntaxIssueCount)
                .functionIssueCount(0) // 保持向后兼容，设为0
                .datatypeIssueCount(0) // 保持向后兼容，设为0  
                .performanceIssueCount(performanceIssueCount)
                .compatibilityIssueCount(compatibilityIssueCount) // 新增兼容性问题统计
                .securityIssueCount(securityIssueCount) // 新增安全问题统计
                .build();
    }

}